﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DAOTravelAgency
{
    public class DAO
    {
        private static TravelAgencyDataContext context = new TravelAgencyDataContext();

        //tours manipulation - start
        public static void AddTour(DateTime startDate, DateTime endDate, decimal price, 
            int destinationId, string description, int? informationId)
        {            
            Tour newTour = new Tour()
            {
                StartDate = startDate,
                EndDate = endDate,
                Price = price,
                DestinationID = destinationId,
                Description = description,
                InformationID = informationId,
                TourReference = String.Empty,
                Visible = true
            };
            try
            {
                context.Tours.InsertOnSubmit(newTour);
                context.SubmitChanges();

                Destination destination = (from dest in context.Destinations
                                           where dest.DestinationID == newTour.DestinationID
                                           select dest).SingleOrDefault();
                if (destination != null)
                {
                    newTour.TourReference = destination.Name + "-" + newTour.TourID.ToString();
                    context.SubmitChanges();
                }
            }
            catch (Exception)
            {
                context = new TravelAgencyDataContext();
            }
        }

        public static void EditTourById(int tourId, DateTime startDate, DateTime endDate, decimal price,
            int destinationId, string description, int? informationId)
        {
            Tour tourToEdit = (from tour in context.Tours
                               where tour.TourID == tourId
                               select tour).SingleOrDefault();
            if (tourToEdit != null)
            {
                tourToEdit.StartDate = startDate;
                tourToEdit.EndDate = endDate;
                tourToEdit.Price = price;
                tourToEdit.DestinationID = destinationId;
                tourToEdit.Description = description;
                tourToEdit.InformationID = informationId;

                context.SubmitChanges();
            }
        }

        public static void DeleteTour(int tourId)
        {
            try
            {
                Tour tourToDelete = (from tour in context.Tours
                                     where tour.TourID == tourId
                                     select tour).SingleOrDefault();
                if (tourToDelete != null)
                {
                    tourToDelete.Visible = false;
                    context.SubmitChanges();
                }
            }
            catch (Exception)
            {
            }            
        }

        public static IQueryable<Tour> FindToursByDestination(string destination)
        {
            Destination currentDestination = (from dest in context.Destinations
                                              where dest.Name == destination
                                              select dest).SingleOrDefault();
            if (currentDestination != null)
            {
                var tours = from tour in context.Tours
                            where currentDestination.DestinationID == tour.DestinationID
                            select tour;
                return tours;
            }
            else
            {
                return null;
            }
        }

        public static IQueryable<Tour> FindToursByPriceRange(decimal minPrice, decimal maxPrice)
        {
            var tours = from tour in context.Tours
                        where (tour.Price >= minPrice && tour.Price <= maxPrice)
                        select tour;
            return tours;
        }

        public static IQueryable<Tour> FindToursByPriceRange(decimal minPrice, decimal maxPrice, IQueryable<Tour> toursFromPreviousFind)
        {
            var tours = from tour in toursFromPreviousFind
                        where (tour.Price >= minPrice && tour.Price <= maxPrice)
                        select tour;
            return tours;
        }

        public static IQueryable<Tour> FindToursByReferenceNumber(string referenceNumber)
        {
            var tours = from tour in context.Tours
                        where tour.TourReference == referenceNumber
                        select tour;
            return tours;
        }

        public static IQueryable<Tour> FindToursByReferenceNumber(string referenceNumber, IQueryable<Tour> toursFromPreviousFind)
        {
            var tours = from tour in toursFromPreviousFind
                        where tour.TourReference == referenceNumber
                        select tour;
            return tours;
        }
        //tours manipulation - end

        //reservation manipulation - start
        private static void AddReservationCustomerRelations(int reservationId, List<int> customers)
        {
            foreach (var item in customers)
            {
                ReservationCustomer newReservationCustomer = new ReservationCustomer()
                {
                    ReservationID = reservationId,
                    CustomerID = item,
                };
                context.ReservationCustomers.InsertOnSubmit(newReservationCustomer);
                context.SubmitChanges();
            }            
        }

        private static bool ChekCustomers(List<int> customers)
        {
            foreach (var item in customers)
            {
                var customer = (from cust in context.Customers
                                where cust.CustomerID == item
                                select cust).SingleOrDefault();
                if (customer == null)
                {
                    return false;
                }
            }
            return true;
        }

        private static void DeleteReservationCustomerRelations(int reservationId)
        {
            IQueryable<int> customers = from reservationCustomer in context.ReservationCustomers
                                  where reservationCustomer.ReservationID == reservationId
                                  select reservationCustomer.CustomerID;
            foreach (var item in customers)
            {
                ReservationCustomer reservationCustomer = new ReservationCustomer()
                {
                    ReservationID = reservationId,
                    CustomerID = item,
                };
                context.ReservationCustomers.DeleteOnSubmit(reservationCustomer);                
            }
            context.SubmitChanges();
        }

        public static void AddReservation(string additionalNotes, int customerId, float discount, int statusId, int tourId, List<int> customers)
        {
            try
            {
                Reservation newReservation = new Reservation()
                {
                    AdditionalNotes = additionalNotes,
                    CustomerID = customerId,
                    Discount = discount,
                    ReservationReference = String.Empty,
                    StatusID = statusId,
                    TourID = tourId,
                    TotalPrice = 0
                };

                context.Reservations.InsertOnSubmit(newReservation);
                if (ChekCustomers(customers)) 
                {
                    context.SubmitChanges();
                    Customer guarantee = (from cust in context.Customers
                                          where cust.CustomerID == customerId
                                          select cust).SingleOrDefault();
                    newReservation.ReservationReference = guarantee.LastName + "-" + newReservation.ReservationID.ToString();

                    decimal price = (from tour in context.Tours
                                     where tour.TourID == tourId
                                     select tour.Price).SingleOrDefault();
                    newReservation.TotalPrice = customers.Count * (decimal)price;
                    newReservation.TotalPrice = newReservation.TotalPrice - ((newReservation.TotalPrice * (decimal)newReservation.Discount)) / 100;
                    context.SubmitChanges();
                }                
            }
            catch (Exception)
            {
                context = new TravelAgencyDataContext();
            }

        }

        public static void EditReservationById(int reservationId, string additionalNotes, int customerId, float discount, int statusId, int tourId, List<int> customers)
        {
            Reservation reservationToEdit = (from reservation in context.Reservations
                                             where reservation.ReservationID == reservationId
                                             select reservation).SingleOrDefault();
            if (reservationToEdit != null)
            {
                //try
                //{
                    reservationToEdit.AdditionalNotes = additionalNotes;
                    reservationToEdit.CustomerID = customerId;
                    reservationToEdit.Discount = discount;
                    reservationToEdit.StatusID = statusId;
                    reservationToEdit.TourID = tourId;

                    decimal price = (from tour in context.Tours
                                     where tour.TourID == tourId
                                     select tour.Price).SingleOrDefault();
                    reservationToEdit.TotalPrice = customers.Count * (decimal)price;
                    reservationToEdit.TotalPrice = reservationToEdit.TotalPrice - ((reservationToEdit.TotalPrice * (decimal)reservationToEdit.Discount / 100));

                    Customer guarantee = (from cust in context.Customers
                                          where cust.CustomerID == customerId
                                          select cust).SingleOrDefault();
                    reservationToEdit.ReservationReference = guarantee.LastName + "-" + reservationId;

                    context.SubmitChanges();
                    if (ChekCustomers(customers))
                    {
                        DeleteReservationCustomerRelations(reservationId);
                        AddReservationCustomerRelations(reservationId, customers);
                    }
               // }
            //    catch (Exception)
            //    {
            //        context = new TravelAgencyDataContext();
            //    }
            }
            
        }
        //reservation manipulation - end
    }
}
